import akshare as ak
import datetime
import SimpleMySqlClass as smc
import MySqlUtil
import MyLogUtil
import MyDateUtil


class StockService:
    def __init__(self):
        print("init StockService")

    def get_hs300_zz500(self):
        hz300_list = self.get_remote_hz300()
        zz500_list = self.get_remote_zz500()
        return hz300_list + zz500_list

    # 000300 沪深300
    # 000905 中正500

    # 获取沪深300
    def get_remote_hz300(self):
        code = "000300"
        return self.get_remote_hz300_zz500(code)

    # 获取中正500
    def get_remote_zz500(self):
        code = "000905"
        return self.get_remote_hz300_zz500(code)

    def get_remote_hz300_zz500(self, code):
        cfg = ak.index_stock_cons_sina(code)
        codeNames = cfg.loc[:, ["symbol", "code", "name"]]
        return codeNames

    # 获取基金重仓股列表
    def get_remote_fund_heavy_weight_stock(self, dt=datetime.datetime.now()):
        """
        data =
         序号               100
         股票代码          600522
         股票简称            中天科技
         报告期       2024-03-31
         基金覆盖家数           126
         持股总数       308658355
         持股总市值      433047.67
         Name: 99, dtype: object
        """
        cur_year = dt.year
        cur_month = dt.month
        query_date = "20240331"
        if cur_month >= 1 and cur_month <= 3:
            query_date = str(cur_year - 1) + "1231"
        elif cur_month >= 4 and cur_month <= 6:
            query_date = str(cur_year) + "0331"
        elif cur_month >= 7 and cur_month <= 9:
            query_date = str(cur_year) + "0630"
        elif cur_month >= 10 and cur_month <= 12:
            query_date = str(cur_year) + "0930"
        else:
            print("异常数据")

        # date="20210630"; choice of {"XXXX0331", "XXXX0630", "XXXX0930", "XXXX1231"}, 其中 XXXX 为年份
        fund_report_stock_info_df = ak.fund_report_stock_cninfo(date=query_date)
        return fund_report_stock_info_df

    # 获取沪深300
    def get_local_hz300(self, limit_num=1000):
        table_name = "t_cfg_hs_300"
        sql = f"select code,name from {table_name} limit {limit_num}"
        return self.get_local_data(sql)

    # 获取中正500
    def get_local_zz500(self, limit_num=1000):
        table_name = "t_cfg_zz_500"
        sql = f"select code,name from {table_name} limit {limit_num}"
        return self.get_local_data(sql)

    #  {"code": "000300", "desc": "沪深300", "tableName": "t_cfg_hs_300"},
    #  {"code": "000905", "desc": "中正500", "tableName": "t_cfg_zz_500"},

    def remote_data_to_local(remote_stock_data, table_name):
        conn = MySqlUtil.get_mysql_conn()
        # 落库
        for index, row in remote_stock_data.iterrows():
            insert_data = {
                "id": 0,
                "symbol": row["symbol"],
                "code": row["code"],
                "name": row["name"],
                "create_time": datetime.datetime.now(),
                "update_time": datetime.datetime.now(),
            }
            in_rs = conn.insertIgnore(table_name, insert_data)
            MyLogUtil.log(f"insert data={insert_data} , rs = {in_rs}")

        # 关闭连接
        conn.close()

    # 获取基金重仓股列表
    def get_local_fund_heavy_weight_stock(self, limit_num=1000):
        table_name = "t_fund_heavy_stock"
        sql = f"select code,name from {table_name} limit {limit_num}"
        self.get_local_data(sql)

    # 获取沪深300
    def get_remote_hz300_to_local(self):
        # 拉取远端数据
        hs300_stock_data = self.get_remote_hz300()
        table_name = "t_cfg_hs_300"
        # 落库
        self.remote_data_to_local(hs300_stock_data, table_name)
        pass

    # 获取中正500
    def get_remote_zz500_to_local(self):
        # 拉取远端数据
        zz500_stock_data = self.get_remote_zz500()
        table_name = "t_cfg_zz_500"
        # 落库
        self.remote_data_to_local(zz500_stock_data, table_name)
        pass

    # 获取基金重仓股列表
    def get_remote_fund_heavy_weight_stock_to_local(self):
        remote_stock_data = self.get_remote_fund_heavy_weight_stock()
        conn = MySqlUtil.get_mysql_conn()
        table_name = "t_fund_heavy_stock"
        # 落库
        for index, row in remote_stock_data.iterrows():
            # 插入新记录
            insert_data = {
                "id": 0,
                "order_no": row["序号"],
                "code": row["股票代码"],
                "name": row["股票简称"],
                "report_date": row["报告期"],
                "fun_com_num": row["基金覆盖家数"],
                "position_volume": row["持股总数"],
                "position_value": row["持股总市值"],
                "create_time": datetime.datetime.now(),
            }
            table_name = "t_fund_stock_info"
            in_rs = conn.insertIgnore(table_name, insert_data)
            MyLogUtil.log(f"insert data={insert_data} , rs = {in_rs}")
            rs_ct = rs_ct + in_rs

        MyLogUtil.log(f"insert {table_name} 基金重仓股, rs = {rs_ct}")

        # 关闭连接
        conn.close()

    # 查询本地数据
    def get_local_data(self, sql):
        conn = MySqlUtil.get_mysql_conn()
        query_rs = conn.get_all(sql)
        conn.close()
        return query_rs

    # 获取日线数据
    def get_remote_daily_data(
        self,
        code,
        start_dt=MyDateUtil.get_datetime(180),
        end_dt=datetime.datetime.now(),
    ):
        start_date_param = start_dt.strftime("%Y%m%d")
        end_date_param = end_dt.strftime("%Y%m%d")

        stock_qfq_data = ak.stock_zh_a_hist(
            symbol=code,
            adjust="qfq",
            start_date=start_date_param,
            end_date=end_date_param,
        )
        # print(stock_qfq_data)
        stock_qfq_data = stock_qfq_data.iloc[:, 0:7]

        # 日期     开盘     收盘     最高     最低     成交量  成交额
        # 列名记得这样定义好
        stock_qfq_data.columns = [
            "date",
            "open",
            "close",
            "high",
            "low",
            "volume",
            "amount",
        ]
        stock_qfq_data["date_time"] = stock_qfq_data.loc[:, ["date"]]
        stock_qfq_data["code"] = code
        # print(stock_qfq_data)
        return stock_qfq_data

    # 获取日线数据
    def get_local_daily_data(
        self,
        code,
        start_dt=MyDateUtil.get_datetime(180),
        end_dt=datetime.datetime.now(),
    ):

        tableName = "t_trade_data_" + code
        start_date_param = start_dt.strftime("%Y-%m-%d")
        end_date_param = end_dt.strftime("%Y-%m-%d")
        sql = f"select `date`, `open`, `high`, `low`, `close`, `volume`, `date_time`, '{code}' as `code` from {tableName} where `date_time` between '{start_date_param}' and '{end_date_param}' order by `date_time` limit 1000"
        stock_data = self.get_local_data(sql)
        return stock_data
